<?php
include 'PHPExcel.php';
include 'PHPExcel/Writer/Excel2007.php';

//获取测试数据
//二维数组testData
$link = new mysqli('127.0.0.1','root','','hxl');
mysqli_set_charset($link,"utf8");
$rst = $link->query("select * from xg_student ");
while ($data = $rst->fetch_assoc()) {
    $testData[] = $data;
}

//导出
function export($arr) {
    //创建一个excel
    $objPHPExcel = new PHPExcel();
    //保存excel—2007格式
    $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
    //设置当前的sheet
    $objPHPExcel->setActiveSheetIndex(0);
    //设置sheet的name
    $objPHPExcel->getActiveSheet()->setTitle('export');


    //遍历数组插入数据
    $row = 'A';
    $column = 1;
    foreach ($arr[0] as $k=>$v) {
        $objPHPExcel->getActiveSheet()->setCellValue($row.$column, $k);
        $row++;
    }
    $row = 'A';
    $column++;
    foreach ($arr as $key=>$value) {

        foreach ($value as $item) {
            $objPHPExcel->getActiveSheet()->setCellValue($row.$column, $item);
            $row++;
        }
        $row = 'A';
        $column++;
    }

    //保存到文件
    //    $objWriter->save("export123.xlsx");


    //直接输出到浏览器下载
    $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate,post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");;
    header('Content-Disposition:attachment;filename="export.xls"');
    header("Content-Transfer-Encoding:binary");
    $objWriter->save('php://output');

}
//export($testData);

//导入
function import() {
    $file = './importdata.xlsx';
    $objRead = new PHPExcel_Reader_Excel2007();
    $obj = $objRead->load($file);  //建立excel对象
    $currSheet = $obj->getSheet(0);   //获取指定的sheet表
    $rowCnt = $currSheet->getHighestRow();   //获取总行数
    $columnH = $currSheet->getHighestColumn();   //取得最大的列号,字母

    $row ='A';

    $columnH++; //字母加一位方便下面判断
    //获取数据到data
    for($column=2;$column<=$rowCnt;$column++) {
        while($row!= $columnH) {
            echo $row.$column;
            $data[$column][] = $currSheet->getCell($row.$column)->getValue();
            $row++;
        }
        $row = 'A';
    }

    var_dump($data);


}
//import();

